Night Owl 9

home *** CD-ROM | disk | FTP | other *** search

/ Night Owl 9 / Night Owl CD-ROM (NOPV9) (Night Owl Publisher) (1993).ISO / 032a / acctxt2.zip / SECURE.TXT < prev next >

Wrap

Text File | 1993-02-10 | 26KB | 510 lines

Microsoft Access Security Purpose ------- The purpose of this document is to explain the Microsoft Access security model. The hope is that if the user understands the intricacies of security, and is conscientious, he will be able to safely and successfully use Microsoft Access security in his databases. Examples of scenarios that have caused difficulty for people in the past have been set apart under the heading of Gotcha. These are intended to give practical examples of common misunderstandings, and to explain why Microsoft Access security works the way it does. Overview -------- Microsoft Access security consists of 2 parts which are stored in different places. Information regarding the permissions that users and groups have on the objects in a database is stored in the database itself. This way the permission information travels with the .mdb file in which the objects exist. All other security information is stored in system.mda. This includes: which Users & Groups exist; which Groups each User belongs to; and User logon passwords. In Microsoft Access parlance, a system.mda defines a "Workgroup". Logging On ---------- Each User and Group has associated with it a Security ID (SID). The SID is a binary string that uniquely identifies the User or Group. When a user logs on, Microsoft Access looks in the MSysAccounts table of the system.mda specified in the user's msaccess.ini for a user of the same name (case- insensitive). If a user with the same name is found, it then validates the password (case-sensitive). If the password matches, the SID of the user is retrieved and saved in an internal structure. The password is only used to validate the user when he logs on. Other than validating the identity of the user when he logs on, it has no effect on security. By default, Microsoft Access first attempts to logon as the user Admin, with a blank password. If this logon fails, the user is presented with the Logon dialog. If a user name and password were specified on the command line (using the /USER and /PWD flags), Microsoft Access first tries to logon using that user name and password. If this logon fails, the user is presented with the Logon dialog. Once logged on, the user's SID is retrieved. This SID is used for all subsequent operations within Microsoft Access. Users and Groups ---------------- Each user can be a member of 1 or more groups. Users & Groups share the same namespace. This means that you can't have both a group and user with the same name. Microsoft Access defines 3 default groups: Admins, Users, and Guests. Whenever a user is added to the system.mda, they are automatically given membership in the group Users, and they cannot be removed from the Users group. There is one exception to this rule---the user Guest CAN be removed from the Users group. The Admins group must always have at least one member. Microsoft Access defines 2 users: Admin and Guest. The user Admin is a member of the Admins and Users groups. The user Guest is a member of the Guests group (only). The pre-defined groups (Admins, Users, and Guests) cannot be deleted. The user Guest cannot be deleted, though the user Admin CAN (and should be to preserve security) if there is at least one other member of the Admins group. Each user and group has a SID associated with it. The SIDs of the Users and Guests groups, and of the Admin and Guest users, are the same in all Microsoft Access installations. The SID of the Admins group, however, is unique across all Microsoft Access installations. The SID of the Admins group is determined when Microsoft Access is installed. This prevents someone who is in the Admins group of one system.mda from being able to have the permissions of any other Admins group. Gotcha: If you delete the user Admin, and then create another user named Admin, the SID of the new Admin will NOT be the same as the SID of the Admin that exists in an unsecure system. This is because the Admin user that you create will have a different PIN that the Admin user that exists in a freshly-installed system.mda. The SID of the Admins group is a function of the Setup disk serial number and the User and Company names given (case-insensitive). Therefore, it is critical that the disks used to install Microsoft Access be kept in a safe place, and that the User and Company names be recorded. In the event that the system.mda has to be recreated, the same installation disks must be used. The SIDs of users and groups that you create are a function of the user or group name (case-sensitive) and Personal Identification Number (PIN) that you specify. For this reason it is critical that the user and group names and their PINs be backed up. If there is ever a need to recreate the system.mda, you will need the name and PIN of each user and group that was in the system.mda. Note that by using different PINs, it possible for users and groups in different system.mda's to have the same name, but they will actually be different accounts as far as Microsoft Access security is concerned because they have different SIDs. Gotcha: When you setup Microsoft Access, the User and Company names are written to Disk #1. If you then give anyone else your disks to setup from, Microsoft Access doesn't prompt for the User and Company name. The Admins group in the system.mda that is created will have the same SID as the Admins group in your system.mda. This means that anyone who uses that new system.mda will have all the permissions that you do, in any databases that you have permissions. While this functionality wasn't intended as a form of copy protection, it is a good incentive to abide by the License Agreement. Permissions ----------- There are 2 types of permissions: Explicit and Implicit. Explicit permissions are those given directly to a user. When they are granted, no other users are affected. Implicit permissions are those granted to a group. When they are granted, all users who are members of the group get the permissions of the group. Implicit permissions belong to the group, not the users. If a user is removed from the group, he no longer has the permissions of the group. If the permissions of the group are changed, all users in the group are affected. When a user attempts to do something, he will get the least restrictive permissions of: 1) all the groups in which he is a member (his Implicit permissions), and 2) those given directly to him (his Explicit permissions.) In the Microsoft Access Permissions dialog, only the Explicit permissions are displayed. This is a limitation of v.1.0. To view implicit permissions, view the permissions of each group that the user is a member of. The Admins group of the system.mda in use when the database was created will always have permission to change permissions on all objects in the database. This permission cannot be taken away by anyone!!! This permission remains even when all permissions have been revoked from the Admins group, and isn't displayed in the Permissions dialog. It should therefore be obvious that it is very important to know and keep track of which system.mda was in use when the database was created. To stress this point, italics will be used throughout this document where it comes up. The Users group initially has Full permissions on all objects created in the database. The Guests group has Read Definition and/or Read Data permissions on them, as appropriate for the object type. Basically, Guests can read all objects and data, but can't change anything. Permissions on an object can be changed by anyone having permissions to do so. The following users have this permission: members of the Admins group of the system.mda in use when the database was created; the creator of the database (it's owner); the creator of the object (it's owner); and anyone who has Full permissions on the object, either explicitly or implicitly. Gotcha: Jane gives Joe a database that she created. Joe is a member of the Users group, and no other groups. He is using a different system.mda (the SID of the Admins group of his system.mda is different from the SID of the Admins group in Jane's system.mda). Jane's database is "unsecure", meaning that the Users group has Full permission on all objects. Joe revokes Full permissions from the Users group on a Table1. From that point forward, Joe can no longer change permissions on that Table1. If he also revoked Read Data permissions, he has prevented himself from being able to read Table1, because he no longer has permission to do so. For both these cases, he just revoked his own permissions on the Table1. Because Joe isn't a member of the Admins group of the system.mda in use when the database was created, and because he didn't create Table1, he's completely locked out of Table1 until Jane (using her own system.mda) gives him permissions again. All users always have permission to open any database and create objects in it. Microsoft Access relies on operating system security to prevent a user from being able to open a database. Gotcha: Even if a user doesn't have an account in your system.mda, he can use any other system.mda in which he has an account to logon to Microsoft Access. Then he can open any database that he can get to through the operating system. Once the database is open, he can see which objects exist, and create new objects. There is no method to prevent creation of objects. It is possible for a user to have permissions on an object in a database, and not be able to see those permissions in the Permissions dialog. This will happen if the user was assigned permissions in the database and then was deleted from the system.mda. The permissions given to the user are stored in the .mdb, and aren't removed when the user is removed. The following steps will show these permissions: 1. Set the Show System Objects option to Yes 2. Give yourself Read Data permissions on the MSysACEs table If you don't have permissions to do this, you're not a member of the Admins group of the system.mda in use when the database was created, and you didn't create the database. 3. Attach to or import the MSysAccounts table from the system.mda currently in use If you don't have permissions to do this, you're not a member of the Admins group of the system.mda currently in use. 4. Run this query: SELECT DISTINCTROW MSysObjects.Name AS Object, MSysAccounts.Name As UserGroup FROM MSysACEs, MSysAccounts, MSysObjects, MSysACEs LEFT JOIN MSysAccounts ON MSysACEs.SID = MSysAccounts.SID, MSysACEs INNER JOIN MSysObjects ON MSysACEs.ObjectId = MSysObjects.Id; This will show the names of all users and groups who have permissions on all objects in the database. If the UserGroup field is blank, it means that the user or group doesn't exist in your system.mda, yet still has some kind of permissions on that object. Disclaimer: The query above works fine in Microsoft Access 1.0. However, the Microsoft Access system tables (MSys*) are officially "undocumented", and are subject to change in future versions. Ownership --------- Ownership is a very important part of Microsoft Access' security model. The SID of the user who creates a database is considered the "owner" of the database. He will always have permission to give himself Full Permission on any object in the database, regardless of who creates the object, or what permissions are on the object. He will therefore also always have permissions to give anyone else permissions on any object in the database. The SID of the user who creates an object is considered the "owner" of the object. He will always have permission to change permissions on the object, even if the database owner has revoked his permissions on the object. The Admins group of the system.mda in use when the database was created will always have permissions to change permissions on any object in that database. The only way to transfer ownership of a database or object is to recreate it. For databases, this means doing a File New Database and importing all the objects from the old database. Table relationships will have to be recreated after importing, as they're not preserved when importing or exporting. For objects, ownership can be transferred by importing or exporting the object, or by copying and pasting it. These operations essentially create a brand new object, one that is completely unsecured. Note that for both of these situations, the person doing the importing/exporting or copy/pasting must have permission to read the objects in the first place. Gotcha: As mentioned above, the Admin and Guest users have the same SID in all system.mda's. It is therefore critical to security that the Admin and Guest users NOT create (are the owner of) any objects or databases. If they do, anyone who has Microsoft Access and can get to the database .mdb file can open it and have Full permission on the objects created by Admin or Guest, and all objects in the database if either of these users created the database. The "Hole" ---------- By default, the Users and Guests groups have Read Data permissions on the MSysObjects table. For various technical reasons, this is necessary to allow the user to do File Load From Query in the Filter window. The astute reader will see that MSysObjects contains a field named "Owner", and he will figure out that it contains the SID of the owner of each object. The astute reader will also realize that the SID field in the MSysAccounts table of system.mda contains his own SID. Because everyone has Read Data permissions on MSysObjects, it would be possible to read the SID of the owner of any given object, and replace his SID in MSysAccounts with it. The next time he logged on, he would have the SID of the owner of the object, and all rights associated with it. He could change permissions on the objects, and therefore do anything he wanted to with it. SIDs are binary fields. When MSysObjects is browsed, the binary fields are truncated at the first null terminator (ASCII 0). Additionally, any characters whose ASCII values are below 32 will display as garbage. All of this essentially prevents someone from being able to edit or copy and paste this value directly. However, it would be a relatively simple matter to change your own SID using Microsoft Access Basic code or a query. Fortunately, there is a way to patch this security hole: 1. Make a backup of you database!!! 2. Open your database 3. Set the Show System Objects option to Yes 4. Select Permissions from the Security menu 5. Give the Users group Read Definition permissions on MSysObjects 6. Revoke Read Definition permissions on MSysObjects from the Users group 7. Repeat steps 5 & 6 for the Guests group This will prevent someone from reading the SID from the Owner field and writing it into his own MSysAccounts table. The downside is that when people who aren't in the Admins group (of the system.mda in use when the database was created) attempt to use the "Load From Query" functionality while in MyApp.mdb, they will get an error that says "No permission for 'MSysObjects'". The only people who will be able to use the "trick" above are the person who created the database, and anyone who is a member of the Admins group of the system.mda in use when the database was created. It is not recommended that you repeat steps 5 & 6 for them. Needless to say, this hole will be plugged in a future version of Microsoft Access. Disclaimer: Again, the Microsoft Access system tables (MSys*) are officially "undocumented", and are subject to change in future versions. Don't count on any information regarding them to be applicable to anything but Access version 1.0. The "Run with Owner's Permissions" Query Property ------------------------------------------------- Microsoft Access queries have an property called "Run with Owner's Permissions" (RWOP). This is in the Query Properties dialog. If this property is checked, the permissions of the owner of the query are the only permissions that are considered when attempting to access tables or queries that the query is dependent on. Gotcha: Joe has Read Definition permissions on the Salary table, but not Read Data. Joe creates Query1, which is based on the Salary table. Joe has left RWOP turned on. Joe is unable to execute the query, because he doesn't have Read Data permissions on the Salary table. This much is intuitive. Jane created the Salary table, and has Full permission on it. Jane tries to execute Query1. She cannot, because the RWOP option of Query1 is turned on, and the owner of the query (Joe) doesn't have Read Data permissions on the Salary table. While this may not be entirely intuitive, it is correct functionality. Only the owner (creator) of a query may save it if the RWOP property is turned on. Even the database owner or members of the Admins group of the system.mda in use when the database was created can't save a query created by another user if the RWOP property is turned on. It would be a security breach to allow them to. Note though, that anyone with Modify Definition permissions on the query can uncheck RWOP and successfully save the query. This may lead to other complications if other people were using the query and depending on RWOP being there. This functionality extends to the QueryDef object as well. Only the owner of a query can change the query using the "Set Q.SQL = MySQLString$" syntax if MySQLString$ ends with "WITH OWNER ACCESS OPTION;". Common Scenarios Securing an Existing Database ----------------------------- You've written an application using Microsoft Access---call it MyApp.mdb. You've created all the objects while you were logged on as Admin (the default.) Now you want to "secure" the database. Here are the steps to follow to do so: 1. Make a backup of MyApp.mdb!!! 2. Choose Change Password from the security menu and give a password to the user Admin. 3. Create a new user and add him to the Admins group. For this example, the user is Joe. 4. Exit Microsoft Access and restart it, giving Joe as the user when the Logon dialog is given. 5. Create a new database---call it MyApp2.mdb This will establish Joe as the database owner. 6. Import all the objects from MyApp.mdb into MyApp2.mdb This will establish Joe as owner of all the objects. 7. If you had any table relationships, recreate them in MyApp2.mdb 8. Revoke Full permissions from the Users group on all objects, leaving them with whatever other permissions you like. You may also want to revoke permissions from the Guests group. 9. If desired, create any of your own groups and users, and give them appropriate permissions on all the objects. 10. Rename MyApp.mdb to MyApp.old and rename MyApp2.mdb to MyApp.mdb. Your database is now secure. The only people who can get into the objects in MyApp.mdb are those you gave permissions to in steps 8 & 9, and anyone who is a member of the Admins group of your system.mda. Unsecuring a Secured Database ----------------------------- You've followed the steps under Securing an Existing Database, above. Joe is the owner of the database and all objects in it. The Admins group of Joe's system.mda has permissions to change permissions on any object in the database. The goal here is to make the user Admin the owner of the database and all objects in it, and to give the Users group Full permissions on all objects. Remember, the SIDs of the user Admin and the Users group are constant in all system.mda's, so this will effectively give everyone Full permissions on all objects in the database, and make them the database owner as well. 1. Make a backup of MyApp.mdb!!! 2. Boot Microsoft Access as a member of the Admins group of the system.mda in use when MyApp.mdb was created. 3. Give the Users group Full permissions on all objects in the database. 4. Edit your msaccess.ini file so that SystemDB points to a system.mda in which no users exist. 5. Exit Microsoft Access and restart it logging on as the user Admin. Note that this Admin MUST be the same Admin that exists in the "clean" system.mda that is created when you setup Microsoft Access so that the SID is correct. 6. Create a new database (call it MyApp2.mdb) 7. Import all the objects from MyApp.mdb into MyApp2.mdb Your database is now completely unsecured. Anyone who can to the MyApp2.mdb file have Full permissions on all the objects in it. Protecting Your Code -------------------- You've written a bunch of Microsoft Access Basic code that you want to distribute, perhaps as a Library Database, but you don't want anyone to be able to read your code. The modules are in MyApp.mdb, and were all created while you were logged on as Admin. Assuming that you're starting from a completely unsecure configuration, here are the steps to secure the code: 1. Make a backup of MyApp.mdb!!! 2. Choose Change Password from the security menu and give a password to the user Admin. 3. Create a new user and add him to the Admins group. For this example, the user is Joe. 4. Exit Microsoft Access and restart it, giving Joe as the user when the Logon dialog is given. 5. Open MyApp.mdb 6. For each module you want secured, do the following: a. Open the module in design mode b. File Save Text to save the module to a text file c. File Close d. Delete the module e. Create a new module f. File Load Text to bring the text file from step b back in g. File Save, giving the original module name This will establish Joe as owner of the modules. 7. Revoke all permissions from the Users and Guests groups on all the modules. All users will still be able to execute the code---there's no way to prevent someone from being able to execute code. Your modules are now secured so that nobody except Joe and members of the Admins group of the system.mda in use when MyApp.mdb was created can read the modules. Moving a Database to Another System.mda --------------------------------------- You are Joe, and have written MyApp.mdb and want to give it to Jane who is using a different system.mda. You want Jane to have complete administrative authority over your database. The problem is this: The Admins group of Jane's system.mda doesn't have any permissions on anything in MyApp.mdb. There are 2 variations of this scenario: Variation #1: MyApp.mdb is completely unsecured (the Users group has Full permissions on everything). The situation here is as follows... The user Admin created MyApp.mdb and all the objects in it, and is therefore the owner of everything. The Admins group of Joe's system.mda has permissions to change permissions on all the objects in MyApp.mdb. The Users group (which has the same SID in all Microsoft Access installations) has Full permissions on all objects in MyApp.mdb. Jane's system.mda is unsecure (the Admin password is blank, and there are no user accounts or groups.) To give MyApp.mdb to Jane, and have her get complete administrative authority over it, follow these steps: 1. Make a backup of MyApp.mdb!!! 2. Boot Microsoft Access using Jane's system.mda. 3. Create a new database---call it MyApp2.mdb This will establish Admin as the database owner. 4. Import all the objects from MyApp.mdb into MyApp2.mdb This will establish the user Admin as owner of all the objects, and give the Admins group of Jane's system.mda permission to change permissions on all objects created in MyApp2.mdb. 5. If you had any table relationships, recreate them in MyApp2.mdb 6. Rename MyApp.mdb to MyApp.old and rename MyApp2.mdb to MyApp.mdb. Variation #2: MyApp.mdb is secured using the steps under Securing an Existing Database. To give MyApp.mdb to Jane, and have the Admins group of her system.mda get complete administrative authority over it, follow these steps: 1. Follow the instructions under Unsecuring a Secured Database, above 2. Follow the instructions under Variation #1, above. For all intents and purposes, Jane is now the "owner" of MyApp.mdb and all objects in it, and Joe is just another user. Under both these scenarios, you can include steps 1-4 and 8-10 of Securing an Existing Database, above, as necessary to make the database secured under Jane's system.mda. Miscellaneous Tidbits --------------------- The User() function in Microsoft Access Basic returns the name of the current user. This is not the same as the SID. As mentioned under Users and Groups, above, it is possible for 2 users in different system.mda's to have the same name but different SIDs. Gotcha: This can cause a problem if 2 people that are using different system.mda's have the same name but different PINs (and therefore different SIDs). For example, if you are using the User() function to log activity, and both are using your database, the activity logging could be inaccurate. These are actually 2 different accounts, even though their user names are the same.